#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

ads_user_activity_sql="
INSERT INTO TABLE wyy_music_warehouse.ads_user_activity
SELECT dt,
       activity_user_count_day as activity_day,
       COUNT(device_id) AS user_count,
       action_type
FROM (SELECT '2025-07-25'               AS dt
           , DATE_SUB('2025-07-25', days_range) AS activity_user_count_day
           , device_id
           , action_type
      FROM wyy_music_warehouse.dws_user_activity_day
               LATERAL VIEW EXPLODE(array(0, 30)) tmp AS days_range
      WHERE dt >= DATE_SUB('2025-07-25', days_range)
        AND dt <= '2025-07-25'
      GROUP BY action_type, device_id,DATE_SUB('2025-07-25', days_range),dt) tmp
GROUP BY action_type, activity_user_count_day, dt
;
"

ADS_USER_NEW_USER_COUNT_SQL="
INSERT INTO TABLE  wyy_music_warehouse.ads_user_new_user_count
SELECT '2025-07-25'                                               AS dt
     , COUNT(new_device_id)                                       AS new_user
     , COUNT(activity_device_id)                                  AS retained_user
     , ROUND(COUNT(activity_device_id) / COUNT(new_device_id), 4) AS retention_rate
FROM (SELECT new.device_id      AS new_device_id,
             new.new_date,
             activity.device_id AS activity_device_id,
             activity.activity_date
      FROM (
               SELECT device_id,
                      dt AS new_date
               FROM wyy_music_warehouse.dws_user_new
               WHERE dt >= DATE_SUB('2025-07-25', 7)
                 AND dt <= '2025-07-25') new
               LEFT JOIN (
          SELECT device_id,
                 dt AS activity_date
          FROM wyy_music_warehouse.dws_user_activity_day
          WHERE dt = '2025-07-25'
          GROUP BY device_id, dt) activity
                         ON new.device_id = activity.device_id) tmp
GROUP BY new_date
;
"


ADS_POPULAR_SONG_RANK_SQL="
INSERT INTO wyy_music_warehouse.ads_popular_song_rank
SELECT
    song_id
    ,song_name
    ,artist_name
    ,count(song_id) AS play_count
    ,if(client_play_duration !='',count(client_play_duration),0) AS play_user_count
    ,avg(client_play_duration) AS avg_duration
    ,if(action_type = 'download', count(song_id), NULL) AS download_count
    ,if(action_type = 'share', count(song_id), NULL) AS share_count
    ,rank() over (ORDER BY count(song_id) DESC) rank
FROM wyy_music_warehouse.dwd_event_log_detail
WHERE dt = '2025-07-25'
GROUP BY song_id,song_name,artist_name,action_type,client_play_duration
;
"


ADS_SONG_PLAY_COMPLETION_RATE_SQL="
INSERT INTO wyy_music_warehouse.ads_song_play_completion_rate
SELECT
    song_id
    ,song_name
    ,song_duration
    ,avg(song_duration) AS avg_play_duration
    ,sum(if(client_play_duration = song_duration,1,0)) / count(song_id) AS completion_rate
    ,count(song_id) AS play_count
FROM wyy_music_warehouse.dwd_event_log_detail
WHERE dt = '2025-07-25'
GROUP BY song_id,song_name,song_duration
;
"

ADS_PROVINCE_PLAY_COUNT_RK_SQL="
INSERT INTO wyy_music_warehouse.ads_province_play_count_rk
SELECT
    client_province
    ,count(song_id) AS play_count
    ,count(user_id) AS user_count
    ,avg(client_play_duration) AS avg_duration
    ,dense_rank()  over (ORDER BY count(song_id) DESC ) rank
FROM wyy_music_warehouse.dwd_event_log_detail
WHERE dt = '2025-07-25'
GROUP BY client_province,song_id,user_id
;
"

ADS_CITY_PLAY_COUNT_PER_SQL="
INSERT INTO wyy_music_warehouse.ads_city_play_count_per
SELECT
    client_city
    ,song_genre
    ,count(song_id) AS play_count
    ,concat((count(song_genre) / (SELECT count(user_id) FROM wyy_music_warehouse.dwd_event_log_detail)) * 100,'%') AS genre_percentage
FROM wyy_music_warehouse.dwd_event_log_detail
WHERE dt = '2025-07-25'
GROUP BY client_city,song_genre,song_id
;
"



